"""
全面修复数据库表缺失列的脚本
检查并添加所有模型中定义的列
"""
import sys
from pathlib import Path
sys.path.insert(0, str(Path(__file__).parent))

from sqlalchemy import create_engine, text
from app.config import settings


def get_engine():
    """获取数据库引擎"""
    if hasattr(settings, 'DATABASE_URL'):
        db_url = settings.DATABASE_URL
    else:
        db_url = f"mysql+pymysql://{settings.MYSQL_USER}:{settings.MYSQL_PASSWORD}@{settings.MYSQL_HOST}:{settings.MYSQL_PORT}/{settings.MYSQL_DATABASE}"
    return create_engine(db_url, echo=False)


def column_exists(conn, table_name, column_name):
    """检查列是否存在"""
    try:
        result = conn.execute(text(f"""
            SELECT COUNT(*) 
            FROM information_schema.COLUMNS 
            WHERE TABLE_SCHEMA = DATABASE() 
            AND TABLE_NAME = '{table_name}' 
            AND COLUMN_NAME = '{column_name}'
        """))
        return result.fetchone()[0] > 0
    except:
        return False


def add_column_if_not_exists(conn, table_name, column_name, column_def):
    """如果列不存在则添加"""
    if not column_exists(conn, table_name, column_name):
        print(f"  → 添加列: {table_name}.{column_name}")
        try:
            conn.execute(text(f"ALTER TABLE `{table_name}` ADD COLUMN `{column_name}` {column_def}"))
            conn.commit()
            print(f"    ✓ 添加成功")
            return True
        except Exception as e:
            print(f"    ✗ 添加失败: {e}")
            conn.rollback()
            return False
    else:
        print(f"  ✓ 列已存在: {table_name}.{column_name}")
        return True


def fix_service_packages_table(conn):
    """修复 service_packages 表的所有列"""
    print("\n修复表: service_packages")
    print("-" * 60)
    
    columns = [
        ('created_at', "datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'"),
        ('updated_at', "datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'"),
    ]
    
    for col_name, col_def in columns:
        add_column_if_not_exists(conn, 'service_packages', col_name, col_def)


def fix_custom_packages_table(conn):
    """修复 custom_packages 表的所有列"""
    print("\n修复表: custom_packages")
    print("-" * 60)
    
    columns = [
        ('created_at', "datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'"),
        ('updated_at', "datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'"),
    ]
    
    for col_name, col_def in columns:
        add_column_if_not_exists(conn, 'custom_packages', col_name, col_def)


def fix_package_features_table(conn):
    """修复 package_features 表的所有列"""
    print("\n修复表: package_features")
    print("-" * 60)
    
    columns = [
        ('feature_category', "varchar(50) DEFAULT NULL COMMENT '功能分类'"),
        ('description', "varchar(200) DEFAULT NULL COMMENT '功能描述'"),
        ('basic_plan', "varchar(50) DEFAULT NULL COMMENT '基础版'"),
        ('standard_plan', "varchar(50) DEFAULT NULL COMMENT '标准版'"),
        ('professional_plan', "varchar(50) DEFAULT NULL COMMENT '专业版'"),
        ('enterprise_plan', "varchar(50) DEFAULT NULL COMMENT '企业版'"),
        ('created_at', "datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'"),
        ('updated_at', "datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'"),
    ]
    
    for col_name, col_def in columns:
        add_column_if_not_exists(conn, 'package_features', col_name, col_def)


def fix_faqs_table(conn):
    """修复 faqs 表的所有列"""
    print("\n修复表: faqs")
    print("-" * 60)
    
    columns = [
        ('tags', "varchar(200) DEFAULT NULL COMMENT '标签'"),
        ('is_hot', "smallint DEFAULT 0 COMMENT '是否热门问题'"),
        ('view_count', "int DEFAULT 0 COMMENT '浏览量'"),
        ('created_at', "datetime DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间'"),
        ('updated_at', "datetime DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '更新时间'"),
    ]
    
    for col_name, col_def in columns:
        add_column_if_not_exists(conn, 'faqs', col_name, col_def)


def insert_sample_data(conn):
    """插入示例数据"""
    print("\n插入示例数据...")
    print("-" * 60)
    
    # 清空现有数据
    print("  → 清空现有数据...")
    try:
        conn.execute(text("TRUNCATE TABLE package_features"))
        conn.execute(text("TRUNCATE TABLE faqs"))
        conn.commit()
        print("    ✓ 清空成功")
    except Exception as e:
        print(f"    ⚠ 清空失败（可能表为空）: {e}")
        conn.rollback()
    
    # 插入 package_features
    print("\n  → 插入 package_features 数据...")
    features_data = [
        ('存储空间', '基础功能', '数据存储容量', '5GB', '20GB', '100GB', '不限', 10, 1),
        ('用户数量', '基础功能', '支持的用户账号数', '10个', '50个', '200个', '不限', 20, 1),
        ('API调用次数', '基础功能', '每月API调用限制', '1000次/月', '10000次/月', '100000次/月', '不限', 30, 1),
        ('技术支持', '服务支持', '技术支持响应时间', '工作日', '7x12小时', '7x24小时', '7x24小时+专属', 40, 1),
        ('数据备份', '安全功能', '数据备份频率', '×', '每周', '每天', '实时', 50, 1),
        ('自定义域名', '高级功能', '支持绑定自定义域名', '×', '√', '√', '√', 60, 1),
        ('SSL证书', '安全功能', 'HTTPS加密支持', '×', '√', '√', '√', 70, 1),
        ('CDN加速', '性能优化', '内容分发网络加速', '×', '×', '√', '√', 80, 1),
        ('负载均衡', '性能优化', '多服务器负载均衡', '×', '×', '×', '√', 90, 1),
        ('定制开发', '增值服务', '支持定制化功能开发', '×', '×', '按需报价', '包含', 100, 1),
    ]
    
    success_count = 0
    try:
        for feature in features_data:
            try:
                conn.execute(text("""
                    INSERT INTO package_features 
                    (feature_name, feature_category, description, basic_plan, standard_plan, 
                     professional_plan, enterprise_plan, sort_order, status)
                    VALUES (:fn, :fc, :desc, :bp, :sp, :pp, :ep, :so, :st)
                """), {
                    'fn': feature[0], 'fc': feature[1], 'desc': feature[2],
                    'bp': feature[3], 'sp': feature[4], 'pp': feature[5],
                    'ep': feature[6], 'so': feature[7], 'st': feature[8]
                })
                success_count += 1
            except Exception as e:
                print(f"    ✗ 插入失败（{feature[0]}）: {e}")
                conn.rollback()
                break
        
        if success_count == len(features_data):
            conn.commit()
            print(f"    ✓ 成功插入 {success_count} 条记录")
        else:
            print(f"    ⚠ 部分成功：{success_count}/{len(features_data)} 条")
    except Exception as e:
        print(f"    ✗ 插入失败: {e}")
        conn.rollback()
    
    # 插入 faqs
    print("\n  → 插入 faqs 数据...")
    faqs_data = [
        ('套餐相关', '如何选择适合的套餐？', '建议根据您的团队规模、项目复杂度和预算来选择。个人开发者可选择基础版，中小企业推荐标准版，大型企业建议选择专业版。我们也提供免费咨询服务，帮助您做出最佳选择。', '套餐,选择', 1, 10, 1),
        ('套餐相关', '套餐可以随时升级或降级吗？', '可以的。您可以随时升级到更高级的套餐，升级立即生效，费用按照差额补齐。降级需要在当前周期结束时生效。', '套餐,升级', 1, 20, 1),
        ('技术支持', '技术支持的响应时间是多久？', '基础版在工作日响应，标准版提供7x12小时支持，专业版提供7x24小时支持。紧急问题会优先处理。', '技术支持,响应时间', 1, 30, 1),
        ('定制开发', '定制开发的流程是什么？', '流程包括：需求沟通→方案设计→合同签订→项目开发→测试验收→部署上线→后期维护。整个过程我们会保持密切沟通。', '定制开发,流程', 1, 40, 1),
        ('定制开发', '定制开发的费用如何计算？', '费用根据项目复杂度、开发周期、团队规模等因素综合评估。我们会先进行需求分析，然后给出详细的报价方案。', '定制开发,费用', 0, 50, 1),
        ('付款方式', '支持哪些付款方式？', '我们支持对公转账、支付宝、微信支付等多种付款方式。大额订单支持分期付款。', '付款,支付', 0, 60, 1),
        ('售后服务', '是否提供售后服务？', '所有套餐都包含相应的售后服务。定制开发项目提供3-12个月不等的免费维护期，之后可购买技术支持套餐继续获得服务。', '售后,维护', 0, 70, 1),
        ('发票问题', '可以开具发票吗？', '可以开具增值税普通发票或专用发票，发票内容可选技术服务费、软件开发费等。', '发票,财务', 0, 80, 1),
    ]
    
    success_count = 0
    try:
        for faq in faqs_data:
            try:
                conn.execute(text("""
                    INSERT INTO faqs 
                    (category, question, answer, tags, is_hot, sort_order, status)
                    VALUES (:cat, :q, :a, :tags, :hot, :so, :st)
                """), {
                    'cat': faq[0], 'q': faq[1], 'a': faq[2],
                    'tags': faq[3], 'hot': faq[4], 'so': faq[5], 'st': faq[6]
                })
                success_count += 1
            except Exception as e:
                print(f"    ✗ 插入失败（{faq[1][:20]}...）: {e}")
                conn.rollback()
                break
        
        if success_count == len(faqs_data):
            conn.commit()
            print(f"    ✓ 成功插入 {success_count} 条记录")
        else:
            print(f"    ⚠ 部分成功：{success_count}/{len(faqs_data)} 条")
    except Exception as e:
        print(f"    ✗ 插入失败: {e}")
        conn.rollback()


def verify_data(conn):
    """验证数据"""
    print("\n" + "="*60)
    print("验证数据...")
    print("-" * 60)
    
    try:
        result = conn.execute(text("SELECT COUNT(*) FROM package_features"))
        pf_count = result.fetchone()[0]
        print(f"  ✓ package_features: {pf_count} 条记录")
        
        result = conn.execute(text("SELECT COUNT(*) FROM faqs"))
        faq_count = result.fetchone()[0]
        print(f"  ✓ faqs: {faq_count} 条记录")
        
        return pf_count, faq_count
    except Exception as e:
        print(f"  ✗ 验证失败: {e}")
        return 0, 0


def main():
    print("="*60)
    print("数据库表全面修复工具")
    print("="*60)
    
    engine = get_engine()
    
    with engine.connect() as conn:
        # 修复 service_packages 表
        fix_service_packages_table(conn)
        
        # 修复 custom_packages 表
        fix_custom_packages_table(conn)
        
        # 修复 package_features 表
        fix_package_features_table(conn)
        
        # 修复 faqs 表
        fix_faqs_table(conn)
        
        # 插入示例数据
        insert_sample_data(conn)
        
        # 验证
        pf_count, faq_count = verify_data(conn)
    
    print("\n" + "="*60)
    if pf_count >= 10 and faq_count >= 8:
        print("✓ 修复成功！所有数据已就绪")
        print("\n下一步：")
        print("  1. 重启后端服务（如果正在运行）")
        print("  2. 刷新管理后台页面")
        print("  3. 测试 套餐功能对照表 和 常见问题 管理")
    else:
        print("⚠ 部分数据可能未成功插入")
        print(f"   package_features: {pf_count}/10")
        print(f"   faqs: {faq_count}/8")
        print("\n建议：")
        print("  1. 检查上方错误信息")
        print("  2. 手动执行 fix_package_tables.sql")
        print("  3. 检查数据库用户权限")
    print("="*60)


if __name__ == "__main__":
    try:
        main()
    except Exception as e:
        print(f"\n✗ 致命错误: {e}")
        import traceback
        traceback.print_exc()
        sys.exit(1)

